﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using HotelManagement.DTO;

namespace HotelManagement.DAO
{
    class RentDetailDAO:DataProvider
    {
        public RentDetailDAO():
            base()
        {

        }
        public bool Insert(RentDetailDTO rentD)
        {
            string strInsert = "insert into RENTDETAIL(RentID,CustomerID,StartDate,EndDate) values(";
            strInsert += rentD.PaperID + ",";
            strInsert += rentD.CustomerID + ",";
            strInsert += "'" + rentD.StartDate + "',";
            strInsert += "'" + rentD.EndDate + "')";
            return base._ExecuteQuery(strInsert);
        }

        public bool Update(RentDetailDTO rentD)
        {
            string strUpdate = "update RENTDETAIL set ";
            strUpdate += "RentDetailID.RentID=" + rentD.PaperID + ",";
            strUpdate += "RentDetailID.CustomerID=" + rentD.CustomerID + ",";
            strUpdate += "RentDetailID.StartDate='" + rentD.StartDate + "'";
            strUpdate += "RentDetailID.EndDate='" + rentD.EndDate + "'";
            strUpdate += " where RentDetailID=" + rentD.PaperDetailID;

            return base._ExecuteQuery(strUpdate);
        }

        public DataTable Select()
        {
            string strSelect = "select * from rentdetail";
            return base._Select(strSelect);
        }

        public DataTable GetAllRentDetailOfARent(int pRentID)
        {
            string strSelect = "select * from rentdetail where rentdetail.rentid = " + pRentID;
            return base._Select(strSelect);
        }
        public DataTable GetAllCustomerOfARent(int pRentID)
        {
            string strSelect="select customer.customerid,customer.name,";
            strSelect+="customer.gender,customer.idno,";
            strSelect += "customer.custypeid,customer.birth,";
            strSelect += "customer.phonenumber,customer.address,";
            strSelect += "customer.occupation,customer.nationality,rentdetail.enddate";
            strSelect += " from (rentdetail inner join customer on rentdetail.customerid=customer.customerid)";
            strSelect += " where rentdetail.rentid = " + pRentID;
            return base._Select(strSelect);
        }
        public bool UpdateEndDate(int pRentDID,DateTime dt)
        {
            string sql = "update rentdetail set rentdetail.enddate = '" + dt + "' where rentdetail.rentdetailid = " + pRentDID;
            return base._ExecuteQuery(sql);
        }

        public RentDetailDTO SelectARentDetail(int pRentID,int pCustomer)
        {
            string sql = "select * from rentdetail where rentdetail.rentid=" + pRentID + " and rentdetail.CustomerID=" + pCustomer;
            DataTable tbl= base._Select(sql);
            if (tbl==null)
            {
                return null;
            }
            RentDetailDTO rentD = new RentDetailDTO();
            rentD.PaperDetailID = int.Parse(tbl.Rows[0]["RentDetailID"].ToString());
            rentD.PaperID = int.Parse(tbl.Rows[0]["RentID"].ToString());
            rentD.CustomerID = int.Parse(tbl.Rows[0]["CustomerID"].ToString());
            rentD.StartDate = DateTime.Parse(tbl.Rows[0]["StartDate"].ToString());
            rentD.EndDate = DateTime.Parse(tbl.Rows[0]["EndDate"].ToString());
            return rentD;
        }

        public DataTable GetAllCustomerOfTheHotel()
        {
            string strSelect = "select customer.customerid,customer.name,";
            strSelect += "customer.gender,customer.idno,";
            strSelect += "customer.custypeid,customer.birth,";
            strSelect += "customer.phonenumber,customer.address,";
            strSelect += "customer.occupation,customer.nationality,rentdetail.rentid";
            strSelect += " from (rentdetail inner join customer on rentdetail.customerid=customer.customerid)";
            strSelect += " where rentdetail.rentid in (select rent.rentid from rent where rent.status=" + CheckInput.STATUS_CHECK_IN + ")";
            return base._Select(strSelect);
        }
    }
}
